Imports System.Data
Imports System.Data.SqlClient

Public Class frmBusqueda
   Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

   Public Sub New()
      MyBase.New()

      'This call is required by the Windows Form Designer.
      InitializeComponent()

      'Add any initialization after the InitializeComponent() call

   End Sub

   'Form overrides dispose to clean up the component list.
   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
      If disposing Then
         If Not (components Is Nothing) Then
            components.Dispose()
         End If
      End If
      MyBase.Dispose(disposing)
   End Sub

   'Required by the Windows Form Designer
   Private components As System.ComponentModel.IContainer

   'NOTE: The following procedure is required by the Windows Form Designer
   'It can be modified using the Windows Form Designer.  
   'Do not modify it using the code editor.
   Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
   Friend WithEvents Label1 As System.Windows.Forms.Label
   Friend WithEvents Label2 As System.Windows.Forms.Label
   Friend WithEvents cboCampos As System.Windows.Forms.ComboBox
   Friend WithEvents editValor As System.Windows.Forms.TextBox
   Friend WithEvents btnAceptar As System.Windows.Forms.Button
   Friend WithEvents btnBuscar As System.Windows.Forms.Button
   Friend WithEvents dgResult As System.Windows.Forms.DataGrid
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
      Me.GroupBox1 = New System.Windows.Forms.GroupBox
      Me.editValor = New System.Windows.Forms.TextBox
      Me.cboCampos = New System.Windows.Forms.ComboBox
      Me.Label2 = New System.Windows.Forms.Label
      Me.Label1 = New System.Windows.Forms.Label
      Me.btnAceptar = New System.Windows.Forms.Button
      Me.btnBuscar = New System.Windows.Forms.Button
      Me.dgResult = New System.Windows.Forms.DataGrid
      Me.GroupBox1.SuspendLayout()
      CType(Me.dgResult, System.ComponentModel.ISupportInitialize).BeginInit()
      Me.SuspendLayout()
      '
      'GroupBox1
      '
      Me.GroupBox1.Controls.Add(Me.editValor)
      Me.GroupBox1.Controls.Add(Me.cboCampos)
      Me.GroupBox1.Controls.Add(Me.Label2)
      Me.GroupBox1.Controls.Add(Me.Label1)
      Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
      Me.GroupBox1.Name = "GroupBox1"
      Me.GroupBox1.Size = New System.Drawing.Size(256, 72)
      Me.GroupBox1.TabIndex = 0
      Me.GroupBox1.TabStop = False
      '
      'editValor
      '
      Me.editValor.Location = New System.Drawing.Point(80, 40)
      Me.editValor.Name = "editValor"
      Me.editValor.Size = New System.Drawing.Size(168, 20)
      Me.editValor.TabIndex = 3
      Me.editValor.Text = ""
      '
      'cboCampos
      '
      Me.cboCampos.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
      Me.cboCampos.Location = New System.Drawing.Point(80, 16)
      Me.cboCampos.Name = "cboCampos"
      Me.cboCampos.Size = New System.Drawing.Size(168, 21)
      Me.cboCampos.TabIndex = 2
      '
      'Label2
      '
      Me.Label2.Location = New System.Drawing.Point(8, 40)
      Me.Label2.Name = "Label2"
      Me.Label2.Size = New System.Drawing.Size(72, 24)
      Me.Label2.TabIndex = 1
      Me.Label2.Text = "Valor"
      '
      'Label1
      '
      Me.Label1.Location = New System.Drawing.Point(8, 16)
      Me.Label1.Name = "Label1"
      Me.Label1.Size = New System.Drawing.Size(72, 16)
      Me.Label1.TabIndex = 0
      Me.Label1.Text = "Campo"
      '
      'btnAceptar
      '
      Me.btnAceptar.Location = New System.Drawing.Point(280, 16)
      Me.btnAceptar.Name = "btnAceptar"
      Me.btnAceptar.Size = New System.Drawing.Size(104, 24)
      Me.btnAceptar.TabIndex = 1
      Me.btnAceptar.Text = "&Aceptar"
      '
      'btnBuscar
      '
      Me.btnBuscar.Location = New System.Drawing.Point(280, 48)
      Me.btnBuscar.Name = "btnBuscar"
      Me.btnBuscar.Size = New System.Drawing.Size(104, 24)
      Me.btnBuscar.TabIndex = 2
      Me.btnBuscar.Text = "&Buscar"
      '
      'dgResult
      '
      Me.dgResult.CaptionFont = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
      Me.dgResult.CaptionVisible = False
      Me.dgResult.DataMember = ""
      Me.dgResult.HeaderForeColor = System.Drawing.SystemColors.ControlText
      Me.dgResult.Location = New System.Drawing.Point(8, 88)
      Me.dgResult.Name = "dgResult"
      Me.dgResult.ReadOnly = True
      Me.dgResult.Size = New System.Drawing.Size(384, 184)
      Me.dgResult.TabIndex = 3
      '
      'frmBusqueda
      '
      Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
      Me.ClientSize = New System.Drawing.Size(400, 273)
      Me.Controls.Add(Me.dgResult)
      Me.Controls.Add(Me.btnBuscar)
      Me.Controls.Add(Me.btnAceptar)
      Me.Controls.Add(Me.GroupBox1)
      Me.MaximumSize = New System.Drawing.Size(408, 300)
      Me.MinimumSize = New System.Drawing.Size(408, 300)
      Me.Name = "frmBusqueda"
      Me.ShowInTaskbar = False
      Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
      Me.Text = "Busqueda"
      Me.GroupBox1.ResumeLayout(False)
      CType(Me.dgResult, System.ComponentModel.ISupportInitialize).EndInit()
      Me.ResumeLayout(False)

   End Sub

#End Region

   Private mstrTabla As String
   Private mstrJoin As String
   Private mstrWhere As String
   Private mdsValores As DataSet
   Private mstrValor As String
   Private mstrCampos As String

   Private mblnBusqueda As Boolean

   Private WithEvents mtbColumn1 As TextBox
   Private WithEvents mtbColumn2 As TextBox
   Private WithEvents mtbColumn3 As TextBox

   Public WriteOnly Property Join() As String
      Set(ByVal Value As String)
         mstrJoin = Value
      End Set
   End Property

   Public WriteOnly Property Busqueda() As Boolean
      Set(ByVal Value As Boolean)
         mblnBusqueda = Value
      End Set
   End Property

   Public WriteOnly Property Where() As String
      Set(ByVal Value As String)
         mstrWhere = Value
      End Set
   End Property

   Public WriteOnly Property Campos() As String
      Set(ByVal Value As String)
         mstrCampos = Value
      End Set
   End Property

   Public WriteOnly Property Tabla() As String
      Set(ByVal Value As String)
         mstrTabla = Value
      End Set
   End Property

   Public ReadOnly Property Valor() As String
      Get
         Valor = mstrValor
      End Get
   End Property

   Private Sub SetGridColumns()
      Dim intColumn As Integer
      Dim blnTextos As Boolean

      Dim ts As DataGridTableStyle = New DataGridTableStyle
      ts.MappingName = mstrTabla

      Try

         Dim dcCampo As DataColumn

         intColumn = 1

         For Each dcCampo In mdsValores.Tables(mstrTabla).Columns

            Dim dgtCol As DataGridTextBoxColumn = New DataGridTextBoxColumn

            dgtCol.MappingName = dcCampo.ColumnName
            dgtCol.HeaderText = dcCampo.ColumnName
            dgtCol.NullText = ""

            If Not blnTextos Then

               Select Case intColumn
                  Case 1
                     mtbColumn1 = CType(dgtCol.TextBox, TextBox)
                     mtbColumn1.AcceptsReturn = False
                     dgtCol.Width = 120
                  Case 2
                     mtbColumn2 = CType(dgtCol.TextBox, TextBox)
                     mtbColumn2.AcceptsReturn = False
                     dgtCol.Width = 2200
                  Case 3
                     mtbColumn3 = CType(dgtCol.TextBox, TextBox)
                     mtbColumn3.AcceptsReturn = False
                     dgtCol.Width = 120
               End Select

            End If

            ts.GridColumnStyles.Add(dgtCol)

            intColumn += 1

            If intColumn = 4 Then
               blnTextos = True
            End If

         Next

      Catch ex As Exception

      Finally

         ' Agrego las Columnas a La Tabla de Estilos
         dgResult.TableStyles.Add(ts)

      End Try

   End Sub

   Private Sub frmBusqueda_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim cnnConn As New SqlConnection
      Dim strTabla As String

      Try

         mdsValores = New DataSet

         If mblnBusqueda Then

            cnnConn.ConnectionString = gstrConnection
            cnnConn.Open()

            Dim daValores As New SqlDataAdapter
            Dim strCampos As String

            If mstrCampos = "" Then
               strCampos = "*"
            Else
               strCampos = mstrCampos
            End If

            If mstrJoin = "" Then
               strTabla = mstrTabla
            Else
               strTabla = mstrJoin
            End If

            daValores.SelectCommand = New SqlCommand("Select " & strCampos & " From " & strTabla, cnnConn)

            daValores.Fill(mdsValores, mstrTabla)

            Dim dcCampo As DataColumn

            For Each dcCampo In mdsValores.Tables(mstrTabla).Columns
               cboCampos.Items.Add(dcCampo.Caption)
            Next

                cboCampos.SelectedIndex = 1

         Else

            dgResult.TabIndex = 0
            dgResult.Location = New System.Drawing.Point(8, 8)
            dgResult.Size = New System.Drawing.Size(384, 264)

            VerTodo()

         End If

         SetGridColumns()

      Catch ex As SqlException

         MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

      Catch ex As Exception

         MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

      Finally

         If cnnConn.State = ConnectionState.Open Then
            cnnConn.Close()
         End If

      End Try

   End Sub

   Private Sub VerTodo()
      Dim strCampos As String
      Dim cnnConn As New SqlConnection
      Dim strTabla As String

      Try

         mdsValores = New DataSet

         cnnConn.ConnectionString = gstrConnection
         cnnConn.Open()

         Dim cmdBuscar As New SqlCommand
         cmdBuscar.Connection = cnnConn

         If mstrCampos <> "" Then
            strCampos = mstrCampos
         Else
            strCampos = "*"
         End If

         If mstrJoin = "" Then
            strTabla = mstrTabla
         Else
            strTabla = mstrJoin
         End If

         cmdBuscar.CommandText = "Select " & strCampos & " From " & mstrTabla & " " & mstrWhere

         Dim daValores As New SqlDataAdapter
         daValores.SelectCommand = cmdBuscar

         daValores.Fill(mdsValores, mstrTabla)

         dgResult.DataSource = mdsValores
         dgResult.DataMember = mstrTabla
         dgResult.Refresh()

      Catch ex As SqlException
         MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Catch ex As Exception
         MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Finally
         If cnnConn.State = ConnectionState.Open Then
            cnnConn.Close()
         End If
      End Try

   End Sub

   Private Sub btnBuscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscar.Click
      Dim strCampos As String
      Dim strCampo As String
      Dim strTipo As String
      Dim strTabla As String
      Dim strCampoJoin As String

      ' Verifico el Campo
      strCampo = cboCampos.Text

      If strCampo = "" Then
         Exit Sub
      End If

      ' Busco el Tipo
      Dim dcCampo As DataColumn
      dcCampo = mdsValores.Tables(mstrTabla).Columns(strCampo)

      strTipo = dcCampo.DataType.ToString()

      If strCampo = "Folio" Then
         strCampoJoin = mstrTabla & ".Folio"
      Else
         strCampoJoin = strCampo
      End If

      Dim cnnConn As New SqlConnection

      Try

         mdsValores = New DataSet

         cnnConn.ConnectionString = gstrConnection
         cnnConn.Open()

         Dim cmdBuscar As New SqlCommand
         cmdBuscar.Connection = cnnConn

         If mstrCampos <> "" Then
            strCampos = mstrCampos
         Else
            strCampos = "*"
         End If

         If mstrJoin = "" Then
            strTabla = mstrTabla
         Else
            strTabla = mstrJoin
         End If

         If strTipo = "System.String" Then
            cmdBuscar.Parameters.Add("@" & strCampo, editValor.Text.Trim & "%")
            cmdBuscar.CommandText = "Select " & strCampos & " From " & strTabla & " Where " & strCampoJoin & " Like @" & strCampo & mstrWhere
         Else
            cmdBuscar.Parameters.Add("@" & strCampo, editValor.Text)
            cmdBuscar.CommandText = "Select " & strCampos & " From " & strTabla & " Where " & strCampoJoin & " = @" & strCampo & mstrWhere
         End If

         Dim daValores As New SqlDataAdapter
         daValores.SelectCommand = cmdBuscar

         daValores.Fill(mdsValores, mstrTabla)

         dgResult.DataSource = mdsValores
         dgResult.DataMember = mstrTabla
         dgResult.Refresh()

      Catch ex As SqlException
         MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Catch ex As Exception
         MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Finally
         If cnnConn.State = ConnectionState.Open Then
            cnnConn.Close()
         End If
      End Try

   End Sub

   Private Sub btnAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAceptar.Click
      Dim strRetVal As String

      Try
         strRetVal = CStr(dgResult.Item(dgResult.CurrentRowIndex, 0))
      Catch ex As Exception
         strRetVal = ""
      Finally
         mstrValor = strRetVal.Trim
      End Try

      Me.Close()

   End Sub

   Private Sub cboCampos_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCampos.Enter
      Me.AcceptButton = btnBuscar
   End Sub

   Private Sub editValor_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles editValor.Enter
      Me.AcceptButton = btnBuscar
   End Sub

   Private Sub dgResult_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgResult.Enter
      Me.AcceptButton = btnAceptar
   End Sub

   Private Sub dgResult_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgResult.KeyUp
      If e.KeyCode = Keys.Return Then
         btnAceptar_Click(Me, System.EventArgs.Empty)
      End If
   End Sub

   Private Sub dgResult_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgResult.DoubleClick
      btnAceptar_Click(Me, System.EventArgs.Empty)
   End Sub

   Private Sub dgResult_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgResult.KeyPress
      btnAceptar_Click(Me, System.EventArgs.Empty)
   End Sub

   Private Sub mtbColumn1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles mtbColumn1.KeyDown, mtbColumn2.KeyDown, mtbColumn3.KeyDown
      If e.KeyCode = Keys.Return Then
         btnAceptar_Click(Me, System.EventArgs.Empty)
      End If
   End Sub

End Class
